package com.css.fxfzypg.modules.rating.task.dao;

import cn.hutool.core.date.DateUtil;
import com.css.fxfzypg.modules.rating.task.model.entity.KpRatingTaskItemScore;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import java.math.BigDecimal;
import java.util.List;

/**
 * 分项得分表管理
 */
@Repository
public class KpRatingTaskItemScoreRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public KpRatingTaskItemScore selectByProvinceIdAndKrtIdAndKesdi2Id(String provinceId, String krtId, String kesdi2Id) {
        try {
            return jdbcTemplate.queryForObject(String.format("select * from kp_rating_task_item_score where province_id = '%s' and krt_id = '%s' and kesdi2_id = '%s' limit 1 offset 0", provinceId, krtId, kesdi2Id), new BeanPropertyRowMapper<>(KpRatingTaskItemScore.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<KpRatingTaskItemScore> selectByProvinceIdAndKrtId(String provinceId, String krtId) {
        String sql = String.format("select * from kp_rating_task_item_score where province_id = '%s' and krt_id = '%s' ", provinceId, krtId);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskItemScore.class));
    }

    public void insert(KpRatingTaskItemScore kpRatingTaskItemScore) {
        namedParameterJdbcTemplate.update("INSERT INTO kp_rating_task_item_score(id, kesdi2_id, default_score, last_score, province_id, krt_id) values(:id, :kesdi2Id, :defaultScore, :lastScore, :provinceId, :krtId)", new BeanPropertySqlParameterSource(kpRatingTaskItemScore));
    }

    public void updateById(KpRatingTaskItemScore kpRatingTaskItemScore) {
        StringBuilder sql = new StringBuilder("update kp_rating_task_item_score set ");
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getId())) {
            sql.append(" id = '").append(kpRatingTaskItemScore.getId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getKesdi2Id())) {
            sql.append(" kesdi2_id = '").append(kpRatingTaskItemScore.getKesdi2Id()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getDefaultScore())) {
            sql.append(" default_score = '").append(kpRatingTaskItemScore.getDefaultScore()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getLastScore())) {
            sql.append(" last_score = '").append(kpRatingTaskItemScore.getLastScore()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getProvinceId())) {
            sql.append(" province_id = '").append(kpRatingTaskItemScore.getProvinceId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getKrtId())) {
            sql.append(" krt_id = '").append(kpRatingTaskItemScore.getKrtId()).append("', ");
        }
        sql.deleteCharAt(sql.lastIndexOf(", "));
        sql.append(" where id = '").append(kpRatingTaskItemScore.getId()).append("' ");
        jdbcTemplate.update(sql.toString());
    }

    public List<String> selectProvinceIdByKrtId(String krtId) {
        return jdbcTemplate.queryForList("select province_id from kp_rating_task_item_score where krt_id = '" + krtId + "' GROUP BY province_id", String.class);
    }

    public void updateByProvinceIdAndKrtIdAndKesdi2Id(KpRatingTaskItemScore kpRatingTaskItemScore) {
        StringBuilder sql = new StringBuilder("update kp_rating_task_item_score set ");
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getId())) {
            sql.append(" id = '").append(kpRatingTaskItemScore.getId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getKesdi2Id())) {
            sql.append(" kesdi2_id = '").append(kpRatingTaskItemScore.getKesdi2Id()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getDefaultScore())) {
            sql.append(" default_score = '").append(kpRatingTaskItemScore.getDefaultScore()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getLastScore())) {
            sql.append(" last_score = '").append(kpRatingTaskItemScore.getLastScore()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getProvinceId())) {
            sql.append(" province_id = '").append(kpRatingTaskItemScore.getProvinceId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskItemScore.getKrtId())) {
            sql.append(" krt_id = '").append(kpRatingTaskItemScore.getKrtId()).append("', ");
        }
        sql.deleteCharAt(sql.lastIndexOf(", "));
        Assert.notNull(kpRatingTaskItemScore.getProvinceId(), "provinceId is null");
        Assert.notNull(kpRatingTaskItemScore.getKrtId(), "krtId is null");
        Assert.notNull(kpRatingTaskItemScore.getKesdi2Id(), " kesdi2Id is null");
        sql.append(" where kesdi2_id = '").append(kpRatingTaskItemScore.getKesdi2Id()).append("' and province_id = '" + kpRatingTaskItemScore.getProvinceId() + "' and krt_id = '" + kpRatingTaskItemScore.getKrtId() + "'");
        jdbcTemplate.update(sql.toString());
    }

    public BigDecimal sumScoreByProvinceIdAndKrtId(String provinceId, String krtId) {
        return jdbcTemplate.queryForObject("select sum(last_score) from kp_rating_task_item_score where province_id = '" + provinceId + "' and krt_id = '" + krtId + "' ", BigDecimal.class);
    }

    public void updateLastScoreByKesdi2Id(String id, BigDecimal expertScore) {
        StringBuilder sql = new StringBuilder("update kp_rating_task_item_score set last_score ='"+expertScore+"' where kesdi2_id ='"+id+"' ");
        jdbcTemplate.update(sql.toString());
    }
}
